import pymysql


def connect():
    print('connect to mysql server...')
    db = pymysql.connect("localhost", "root", "123456", "newschema")
    print('connect success')
    return db


def insert(db, sql):
    cursor = db.cursor()  # obtain the cursor
    try:
        # execute sql
        cursor.execute(sql)
        # commit
        db.commit()
    except:
        # Rollback in case there is any error
        print('插入数据失败!'+sql)
        db.rollback()


def close(db):
    db.close()


def querydb(db, sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        return results
    except:
        print("Error: unable to fecth data")


def deletedb(db, sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except:
        print('删除数据失败!')
        db.rollback()


def updatedb(db, sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except:
        print('更新数据失败!')
        db.rollback()
